-- Nested Set SQL design for Spica 
-- @since  April 21, 2009
-- @author pcdinh
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL,
  `parent_id` int(11) default NULL,
  `lft` int(11) default NULL,
  `rgt` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index_categories_on_parent_id_and_name` (`parent_id`,`name`)
)

-- 1. Create a parent category at first level
INSERT INTO categories 
  (category_id, parent_id, left_side, right_side, depth_level)
VALUES
  (category_id_seq.nextval, 0, MAX(left_side) + 1, MAX(left_side) + 2, 0)
  
-- 2. Gets all chidren/ancestor of the node with ID: :cid (retrieves a list of nodes beneath a tree)
SELECT c1.* 
FROM categories c1, 
     (SELECT left_side, right_side 
      FROM categories WHERE category_id = :cid) c2 
WHERE c1.left_side > c2.left_side
AND c1.left_side < c2.right_side

SELECT c1.* 
FROM categories c1, categories c2 
WHERE c2.category_id = :cid
AND c1.left_side > c2.left_side
AND c1.left_side < c2.right_side

-- 3. Counts the children/ancestor
SELECT (right_side - left_side - 1)/2 
FROM categories c1,
     (SELECT left_side 
      FROM categories 
      WHERE category_id = :cid) c2 
WHERE c1.left_side = c2.left_side

-- 4. Finds all the ancestors/parents of a node
SELECT c1.* 
FROM categories c1, 
     (SELECT left_side, right_side 
      FROM categories 
      WHERE category_id = :cid) c2 
WHERE c1.left_side < c2.left_side
AND c1.right_side > c2.right_side

-- 5. Deletes all ancestors of a node
DELETE FROM categories
WHERE category_id IN (
  SELECT category_id
  FROM categories c1, 
       (SELECT left_side, right_side 
        FROM categories 
        WHERE category_id = :cid) c2 
  WHERE c1.left_side > c2.left_side
  AND c1.left_side < c2.right_side    
)

DELETE FROM categories
WHERE parent_id = :cid

-- 6. Find first level nodes
SELECT * FROM categories
WHERE parent_id IS NULL

SELECT * FROM categories
WHERE depth_level = 0

-- 7. Find the nearest node to the left of the same level (before a node ID)
SELECT category_id 
FROM categories c1, categories c2
WHERE c2.category_id = :cid 
AND c1.right_side = c2.left_side - 1

-- 8. Find the nearest node to the right of the same level (after a node ID)
SELECT category_id 
FROM categories c1, categories c2
WHERE c2.category_id = :cid 
AND c1.left_side = c2.right_side + 1

-- 9. Inserts a node between 2 nodes (before B XOR after A)
-- A - B
--   ^
--   |
--   C 
-- 9.1 After A (:cid)
-- Keeps A numbering
---- C left  = A right + 1
---- C right = A right + 2
---- C parent ID = A parent ID
---- If :cid IS NULL, insert a first level node to the end 
SELECT @maxRight := MAX(right_side) 
FROM categories

INSERT INTO categories 
  (category_id, parent_id, left_side, right_side, depth_level)
VALUES
  (seq.nextval, 0, @maxRight + 1, @maxRight + 2, 0);
  
---- If :cid IS NOT NULL  
SELECT @parentId := parent_id, @maxRight := right_side, @depth_level := depth_level 
FROM categories
WHERE category_id = :cid
------ Shift all nodes next to the right of A to 2 units to clear the way for the new node
UPDATE categories
SET left_side = left_side + 2,
    right_side = right_side + 2
WHERE right_side > @maxRight 
------ Insert new node
INSERT INTO categories 
  (category_id, parent_id, left_side, right_side, depth_level)
VALUES
  (seq.nextval, @parentId, @maxRight + 1, @maxRight + 2, @depth_level)
-- 9.2 Before B (:cid)
-- It is a good idea to insert a new node into the current position of B
----- Gets B information
SELECT @parentId := parent_id, @maxLeft := left_side, @maxRight := right_side, @depth_level := depth_level 
FROM categories
WHERE category_id = :cid
---- Shift all nodes (including B) next to B to the right to 2 units
UPDATE categories
SET left_side = left_side + 2,
    right_side = right_side + 2
WHERE left_side >= @maxLeft
---- Insert a new node into the gap (which was the position of node B before the shift)
INSERT INTO categories 
  (category_id, parent_id, left_side, right_side, depth_level)
VALUES
  (seq.nextval, @parentId, @maxLeft, @maxRight, @depth_level)
  
-- 10. Move all accessors/children of a node (:cid) to a new parent node (:pcid).  
-- Always inserts into the end
--- Finds the new parent node information: 
--- @maxRight + 1 will be used as min left_side in the sub-tree after being moved
SELECT @maxRight := right_side - 1, @depth_level := depth_level 
FROM categories
WHERE category_id = :pcid
---- Calculate node count in the sub-tree
SELECT @nodeCount := FLOOR((right_side - left_side - 1)/2), 
       @minTreeLeft := left_side + 1, 
       @maxTreeRight := right_side - 1
FROM categories
WHERE category_id = :cid
--- The new sub-stree will be inserted after the last immediate accessors of the new parent node: 
--- last accessor right_side and parent node right_side
--- Create a gap between last accessor right_side and parent node right_side, based on new node count
UPDATE categories
SET left_side  = left_side + @nodeCount,
    right_side = right_side + @nodeCount 
WHERE left_side > @maxRight -- Update parent node right_side also    
--- Move the sub-tree to the new parent node
UPDATE categories
SET left_side   = @maxRight,
    right_side  = @maxRight + 1,
    parent_id   = :pcid,
    depth_level = @depth_level + 1    
WHERE left_side >= @minTreeLeft
AND right_side  <= @maxTreeRight 
AND ((SELECT @maxRight := @maxRight + 1) > 0)
